<?php
require_once(Kohana::find_file('vendor','phprpc/phprpc_client'));
/**
 *
 * @author spring
 * @version 2014-6-7 上午9:42:05
 */
class  Controller_Report extends Controller_Admin {

    /**
     *出入库明细单报表查询
     *
     */
    public function before()
    {
        try {
            ACL::Required('kucun detail report');
        } catch ( HTTP_Exception_403 $e ) {
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect ( URL::base (true) );
        }
        parent::before();
    }
    /**
     *
     * Description
     * 入库明细报表显示
     */
    public function action_index()
    {
        $this->title='入库明细报表';
        $warehouses = Kohana::$config->load('warehouse')->warehouses;
        $status = DB::select()->from('statusdics')->where('table','=','warehouseorders')->where('colum_name','=','type')
            ->execute('newerp')->as_array();
        $content=View::factory("delivery/in_report");
        $content->set('export_tax_rebate',Kohana::$config->load('export_tax_rebate')->as_array());
        $content->set('enterprise_dominant',Kohana::$config->load('enterprise_dominant')->as_array());
        $content->set('warehouses', $warehouses);
        $content->set('status', $status);
        $this->response->body($content);
    }
    /**
     *
     * Description
     * ajax 入库明细报表的ajax查询控制器，根据查询条件查出结果
     */
    public function action_in_search()
    {
        $tablename = 'warehouseorders';
        $result = $this->fenye($tablename);
        $export_tax_rebate   = Kohana::$config->load('export_tax_rebate')->as_array();
        $enterprise_dominant = Kohana::$config->load('enterprise_dominant')->as_array();
        $suppliers = $types = $users = array();
        if(!empty($result['datas'])) {
            $supplier_ids = $user_ids = array();
            foreach($result['datas'] as $row) {
                $supplier_ids[] = (int)$row['supplier_id'];
                $user_ids[] = (int)$row['warehouse_man'];
            }
            $sql = "SELECT DISTINCT supplier_id,supplier_name FROM vmi_supplier_skus WHERE supplier_id IN (" . implode(',', $supplier_ids) . ")";
            $suppliers = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array('supplier_id', 'supplier_name');
            $sql = "SELECT id,nick FROM users WHERE id IN (" . implode(',', $user_ids) . ")";
            $users = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array('id', 'nick');
            $types = DB::select('number', 'value')
                ->from('statusdics')
                ->where('table','=','warehouseorders')
                ->where('colum_name', '=', 'type')
                ->execute('newerp')
                ->as_array('number', 'value');
        }
        $view = View::factory('delivery/in_report_table')
            ->set('datas',$result['datas'])
            ->set('totles',$result['totles'])
            ->set('totlesall',$result['totlesall'])
            ->set('export_tax_rebate',$export_tax_rebate)
            ->set('enterprise_dominant',$enterprise_dominant)
            ->set('suppliers',$suppliers)
            ->set('users',$users)
            ->set('types',$types)
            ->bind('pager_links',$result['pager_links'])
            ->render();
        echo $view;
        exit;
    }

    /**
     *
     * Description
     * 出库明细报表显示
     */
    public function action_out()
    {
        $this->title='出库明细报表';
        $content = View::factory("delivery/out_report");
        // $warehouse = basic::get_warehouse_list();
        $warehouses = Kohana::$config->load('warehouse')->warehouses;
        $content->set('export_tax_rebate',Kohana::$config->load('export_tax_rebate')->as_array());
        $content->set('enterprise_dominant',Kohana::$config->load('enterprise_dominant')->as_array());
        $content->set('warehouses', $warehouses);
        $sql="SELECT `number`,`value` FROM `statusdics` WHERE `table`='deliveryorders' AND `colum_name`='type'";
        $deliveryTypes=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('number','value');
        $content->set('deliveryTypes',$deliveryTypes);
        $this->response->body($content);
    }

    /**
     *
     * Description
     * ajax 出库明细报表的ajax查询控制器，根据查询条件查出结果
     */
    public function action_out_search(){
        $tablename = 'deliveryorders';
        $result = $this->fenye($tablename);
        $suppliers = $types = $users = array();
        if(!empty($result['datas'])) {
            $supplier_ids = $user_ids = array();
            foreach($result['datas'] as $row) {
                $supplier_ids[] = (int)$row['supplier_id'];
                $user_ids[] = (int)$row['delivery_man'];
            }
            $sql = "SELECT DISTINCT supplier_id,supplier_name FROM vmi_supplier_skus WHERE supplier_id IN (" . implode(',', $supplier_ids) . ")";
            $suppliers = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array('supplier_id', 'supplier_name');
            $sql = "SELECT id,nick FROM users WHERE id IN (" . implode(',', $user_ids) . ")";
            $users = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array('id', 'nick');
            $types = DB::select('number', 'value')
                ->from('statusdics')
                ->where('table','=','deliveryorders')
                ->where('colum_name', '=', 'type')
                ->execute('newerp')
                ->as_array('number', 'value');
        }
        $view = View::factory('delivery/out_report_table')
            ->set('datas',$result['datas'])
            ->set('totles',$result['totles'])
            ->set('totlesall',$result['totlesall'])
            ->set('suppliers',$suppliers)
            ->set('users',$users)
            ->set('types',$types)
            ->bind('pager_links', $result['pager_links'])
            ->render();
        echo $view;
        exit;
    }



    public function action_in_edit() {
        $warehouseInfo = Delivery::get_delivery('warehouseorders',$_GET['id']);
        $warehouse   = delivery::get_warehouse_list();
        $enterprise_dominants = Kohana::$config->load('enterprise_dominant')->as_array();
        if(Basic::checktime($warehouseInfo['warehouse_date'])){
            $jsonarr = array('status'=>'y','view'=>'关账时间段内不能修改');
        }else {
            $status = Delivery::get_alldelivery_status('warehouseorders','type');
            $view = View::factory("delivery/in_edit")->set('status',$status)->set('warehouse',$warehouse)
                ->set('enterprise_dominants', $enterprise_dominants)->set('warehouseInfo', $warehouseInfo)->render();
            $jsonarr=array('status'=>'y','view'=>$view);
        }
        echo json_encode($jsonarr);
        exit();
    }


    public function action_in_edit_to() {
        $tablename = 'inventories';
        $pre = Pandian::getListInfoCondition(Pandian::getListPre($tablename), array(
            'sku' => $_POST['sku'], 'warehouseid' => $_POST['warehouseid'], 'enterprise_dominant' => $_POST['enterprise_dominant'],
            'start_time' => date('Y-m-d')), true, $tablename);
        $datas = Pandian::getList(1, 0, $pre);
        $datas = Pandian::excute_data($datas,date('Y-m-d H:i:s'));
        $quantity = isset($datas[0]['quantity']) ? $datas[0]['quantity']:0;
        $sy = $quantity + $_POST['warehouse_quantity'] - $_POST["re_quantity"];
        if($sy >= 0){
            if(Warehouse::update_warehouse($_POST)){
                $jsonarr = array('status'=>'y','info'=>'修改成功');
            }else {
                $jsonarr = array('status'=>'n','info'=>'修改失败或者记录没做任何修改');
            }
        }else {
            $jsonarr = array('status'=>'n','info'=>'修改失败，目前库存只有'.$quantity.'个');
        }
        echo json_encode($jsonarr);
        exit() ;
    }



    public function action_out_edit() {
        $delivery = Delivery::get_delivery('deliveryorders',$_GET['id']);
        $warehouse = delivery::get_warehouse_list();
        $enterprise_dominants = Kohana::$config->load('enterprise_dominant')->as_array();
        if (Basic::checktime($delivery['delivery_date'])) {
            $jsonarr = array('status'=>'y','view'=>'关账时间段内不能修改');
        } else {
            $status = Delivery::get_alldelivery_status('deliveryorders','type');
            $view = View::factory("delivery/out_edit")->set('status',$status)->set('warehouse',$warehouse)
                ->set('enterprise_dominants', $enterprise_dominants)->set('delivery',$delivery)->render();
            $jsonarr = array('status'=>'y','view'=>$view);
        }
        echo json_encode($jsonarr);
        exit() ;
    }


    public function action_out_edit_to()
    {
        $tablename = 'inventories';
        $pre = Pandian::getListInfoCondition(Pandian::getListPre($tablename), array(
            'sku' =>$_POST['sku'], 'warehouseid' => $_POST['warehouseid'], 'enterprise_dominant' => $_POST['enterprise_dominant'],
            'start_time'=>date('Y-m-d')), true, $tablename);
        $datas = Pandian::getList(1,0,$pre);
        $datas = Pandian::excute_data($datas,date('Y-m-d H:i:s'));
        $quantity = isset($datas[0]['quantity'])?$datas[0]['quantity']:0;
        $sy = $quantity-$_POST['quantity']+$_POST["re_quantity"];
        if($sy >= 0){
            if (Delivery::update_delivery($_POST)){
                $jsonarr = array('status'=>'y','info'=>'修改成功');
            } else {
                $jsonarr = array('status'=>'n','info'=>'修改失败或者记录没做任何修改');
            }
        }else {
            $quantity = $_POST["re_quantity"]+$quantity;
            $jsonarr = array('status'=>'n','info'=>'修改失败，库存不足，目前只有'.$quantity.'个');
        }
        echo json_encode($jsonarr);
        exit() ;
    }

    /**
     *
     * Description
     * 销售出库统计表显示
     */
    public function action_out_count()
    {
        $this->title='销售出库统计报表';
        $content=View::factory("delivery/out_report_count");
        $this->response->body($content);
    }

    /**
     *
     * Description
     * ajax 销售出库统计表的ajax查询控制器，根据查询条件查出结果
     */
    public function action_out_count_search()
    {
        $tablename='deliveryorders';
        $result=$this->pubudata($tablename);
        $view=View::factory('delivery/out_report_count_table')->set('datas',$result['datas'])
            ->set('totlesall',$result['totlesall'])
            ->set('totles',$result['totles'])
            ->render();
        echo $view;
    }

    public  function  action_in_count(){

        $this->title='采购入库统计表';
        $export_tax_rebate   = Kohana::$config->load('export_tax_rebate')
            ->as_array();
        $enterprise_dominants = Kohana::$config->load('enterprise_dominant')
            ->as_array();
        $content=View::factory("delivery/in_report_count");
        $content->set('enterprise_dominants',$enterprise_dominants)
            ->set('export_tax_rebate',$export_tax_rebate);
        $this->response->body($content);
    }

    public function action_in_count_search()
    {
        $tablename='warehouseorders';
        $result=$this->pubudata($tablename);
        $view=View::factory('delivery/in_report_count_table')
            ->set('datas',$result['datas'])
            ->set('totlesall',$result['totlesall'])
            ->set('totles',$result['totles'])
            ->render();
        echo $view;
    }

    /*
     * 入库明细报表下载gqc20150430新方法下载
     */
    public function action_newDownload() {
        set_time_limit(0);
        ini_set('memory_limit','1024M');
        $pre = Report::getListInfoCondition(Report::getListPre('warehouseorders'), $_GET, true, 'warehouseorders');
        $lists = $pre->execute('newerp')->as_array();
        //入库明细报表添加采购注释
        $pu_ids_arr = Position::array_columns($lists,'purchaseorders_id');
        if(array_sum($pu_ids_arr)>0){
            $pu_remark_arr = DB::select('id','remark')
                ->from('purchaseorders')
                ->where('id','in',array_unique($pu_ids_arr))
                ->execute('newerp')
                ->as_array('id','remark');
        }

        $export_tax_rebate = Kohana::$config->load('export_tax_rebate')->as_array();
        $enterprise_dominant = Kohana::$config->load('enterprise_dominant')->as_array();
        $warehouse = Kohana::$config->load('vmi_warehouse')->as_array();
        $su_ids = $op_mans = $op_ns = $su_ns = array();
        if(!empty($lists)){
            foreach( $lists as $list) {
                if(!empty($su_ids)) {
                    if(!in_array($list['supplier_id'], $su_ids)) {
                        $su_ids[] = $list['supplier_id'];
                    }
                } else {
                    $su_ids[] = $list['supplier_id'];
                }

                if(!empty($op_mans)) {
                    if(!in_array($list['warehouse_man'], $op_mans)) {
                        $op_mans[] = $list['warehouse_man'];
                    }
                } else {
                    $op_mans[] = $list['warehouse_man'];
                }
            }

            //入库类型
            $was_type = DB::select('number','value')->from('statusdics')
                ->where('table', '=', 'warehouseorders')
                ->and_where('colum_name', '=', 'type')
                ->execute('newerp')
                ->as_array('number', 'value');
            //供应商名字
            if(!empty($su_ids)) {
                $sri = "SELECT supplier_id ,supplier_name FROM vmi_supplier_skus WHERE supplier_id in ('".join("','",$su_ids)."')";
                $su_names = DB::query(Database::SELECT,$sri)->execute('newerp')->as_array();
                foreach($su_names as $n){
                    $su_ns[$n['supplier_id']] = $n['supplier_name'];
                }
            }

            //操作人员
            if(!empty($op_mans)) {
                $op_ns = DB::select('id', 'nick')->from('users')->where('id', 'in', $op_mans)->execute()->as_array('id', 'nick');
            }
            // $list_str = '预约单号,仓库,公司主体,是否出口退税,入库单号,SKU,品名,储位,入库量,单价,金额,成本,供应商,规格,入库时间,入库类型,备注,采购备注,操作人,批次号'."\n";
            $list_str = '入库单号,SKU,中文名称,入库量,仓库,批次号,入库时间,入库类型,预约单号,供应商,公司主体,退税类型,储位,单价,成本,金额,备注,采购备注,操作人'."\n";
            foreach($lists as $list) {
                $type = isset($was_type[$list['type']]) ? $was_type[$list['type']] : '';
                $su_name = isset($su_ns[$list['supplier_id']]) ? $su_ns[$list['supplier_id']] : '';
                $op_man_name =  isset($op_ns[$list['warehouse_man']]) ? $op_ns[$list['warehouse_man']] : '';
                $list['pu_remark'] =  $pu_remark_arr[$list['purchaseorders_id']];
                // $list_str .= "{$list['purchaseorders_id']},{$warehouse[$list['warehouseid']]},{$enterprise_dominant[$list['enterprise_dominant']]},{$export_tax_rebate[$list['export_tax_rebate']]},{$list['id']},{$list['sku']},\"{$list['sku_name']}\",\"{$list['storage_position']}\",{$list['warehouse_quantity']},{$list['single_price']},{$list['money']},{$list['cost']},\"{$su_name}\",\"{$list['sku_standard']}\",\"{$list['warehouse_date']}\",\"{$type}\",\"{$list['remark']}\",\"{$list['pu_remark']}\",\"{$op_man_name}\",\"{$list['batch_code']}\"\n";
                $list_str .= "{$list['id']},{$list['sku']},\"{$list['sku_name']}\",{$list['warehouse_quantity']},{$warehouse[$list['warehouseid']]},\"{$list['batch_code']}\",\"{$list['warehouse_date']}\",\"{$type}\",{$list['purchaseorders_id']},\"{$su_name}\",{$enterprise_dominant[$list['enterprise_dominant']]},{$export_tax_rebate[$list['export_tax_rebate']]},\"{$list['storage_position']}\",{$list['single_price']},{$list['cost']},{$list['money']},\"{$list['remark']}\",\"{$list['pu_remark']}\",\"{$op_man_name}\"\n";
            }
        }
        ob_end_flush();
        header("Content-type:text/csv");
        header("Content-Disposition:attachment;filename=入库明细报表.csv"); //“生成文件名称”=自定义
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        echo $list_str;
        exit;
    }

    /*
     * 出库明细报表下载gqc20150430新方法下载
     */
    public function action_newDownloadOut()
    {
        set_time_limit(0);
        ini_set('memory_limit','1024M');

        $pre= Report::getListInfoCondition(Report::getListPre('deliveryorders'), $_GET, true, 'deliveryorders');
        $lists=$pre->execute('newerp')->as_array();
        $su_ids = $op_mans = $op_ns = $su_ns = array();
        $export_tax_rebate = Kohana::$config->load('export_tax_rebate')->as_array();
        $enterprise_dominant = Kohana::$config->load('enterprise_dominant')->as_array();
        $warehouse = Kohana::$config->load('vmi_warehouse')->as_array();

        if(!empty($lists)){
            foreach( $lists as $list) {
                if(!empty($op_mans)) {
                    if(!in_array($list['delivery_man'], $op_mans)) {
                        $op_mans[] = $list['delivery_man'];
                    }
                } else {
                    $op_mans[] = $list['delivery_man'];
                }
                $su_ids[] = (int)$list['supplier_id'];
            }

            //入库类型
            $was_type = DB::select('number','value')->from('statusdics')
                ->where('table', '=', 'deliveryorders')
                ->and_where('colum_name', '=', 'type')
                ->execute('newerp')
                ->as_array('number', 'value');

            //操作人员
            if(!empty($op_mans)) {
                $op_ns = DB::select('id', 'nick')->from('users')->where('id', 'in', $op_mans)->execute()->as_array('id', 'nick');
            }
            if(!empty($su_ids)) {
                $sql = "SELECT DISTINCT supplier_id,supplier_name FROM vmi_supplier_skus WHERE supplier_id IN (" . implode(',', $su_ids) . ")";
                $su_ns = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array('supplier_id', 'supplier_name');
            }
            // $list_str = '订单号,出库单号,仓库,公司主体,是否出口退税,SKU,品名,储位,出库量,单价,金额,成本,规格,出库时间,出库类型,备注,操作人'."\n";
            $list_str = '出库单号,SKU,中文名称,出库量,仓库,出库时间,出库类型,供应商,公司主体,储位,订单号,单价,成本,金额,备注,操作人'."\n";
            foreach($lists as $list) {
                $type = isset($was_type[$list['type']]) ? $was_type[$list['type']] : '';
                $su_name = isset($su_ns[$list['supplier_id']]) ? $su_ns[$list['supplier_id']] : '';
                $op_man_name =  isset($op_ns[$list['delivery_man']]) ? $op_ns[$list['delivery_man']] : '';
                // $list_str .= "{$list['order_id']},{$list['id']},{$warehouse[$list['warehouseid']]},{$enterprise_dominant[$list['enterprise_dominant']]},{$export_tax_rebate[$list['export_tax_rebate']]},{$list['sku']},\"{$list['sku_name']}\",\"{$list['storage_position']}\",{$list['quantity']},{$list['single_price']},{$list['money']},{$list['cost']},\"{$list['sku_standard']}\",\"{$list['delivery_date']}\",\"{$type}\",\"{$list['remark']}\",\"{$op_man_name}\"\n";
                $list_str .= "{$list['id']},{$list['sku']},\"{$list['sku_name']}\",{$list['quantity']},\"{$warehouse[$list['warehouseid']]}\",\"{$list['delivery_date']}\",\"{$type}\",\"{$su_name}\",{$enterprise_dominant[$list['enterprise_dominant']]},\"{$list['storage_position']}\",{$list['order_id']},{$list['single_price']},{$list['cost']},{$list['money']},\"{$list['remark']}\",\"{$op_man_name}\"\n";
            }
        }
        ob_end_flush();
        header("Content-type:text/csv");
        header("Content-Disposition:attachment;filename=出库明细报表.csv"); //“生成文件名称”=自定义
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        echo $list_str;
        exit;
    }

    /*
     * 入库明细报表下载gqc20150424修改
     */
    public function action_downWare () {
        set_time_limit(0);
        header('Content-Type: application/download'); //下载头
        header('Content-Transfer-Encoding: binary'); //二进制
        header('Content-Disposition: attachment; filename='.iconv('UTF-8','GB18030//IGNORE','入库明细报表').'.csv'); //文件名
        header('Pragma:no-cache'); //不缓存

        $path = dirname(dirname(dirname(dirname(dirname(__FILE__))))).'/disk/'.$_GET['md5'].'.csv';
        $fp   = fopen($path,'r');
        flock($fp,LOCK_SH); //加共享锁
        while(!feof($fp)){ //读取数据
            echo fread($fp,8192);
        }
        fclose($fp);

        exit;
    }

    public function action_buildCsv () {
        set_time_limit(0);
        $_GET['date'] = $_SERVER['REQUEST_TIME'];
        $md5 = md5(join('::',$_GET));

        $file = dirname(dirname(dirname(dirname(dirname(__FILE__)))))."/disk/".$md5.".csv";

        if(!is_file($file)) {
            $fp =fopen($file, 'a+');
            flock($fp, LOCK_EX);
            ftruncate($fp, 0);

            fwrite($fp,iconv('UTF-8','GB18030//IGNORE','采购单号,入库单号,SKU,品名,储位,入库量,单价,金额,成本,供应商,规格,入库时间,入库类型,备注,操作人,批次号')."\r\n");

            $pre= Report::getListInfoCondition(Report::getListPre('warehouseorders'), $_GET, true, 'warehouseorders');
            $lists=$pre->execute('newerp')->as_array();
            $su_ids = $op_mans = $op_ns = $su_ns = array();
            if(!empty($lists)){
                foreach( $lists as $list) {
                    if(!empty($su_ids)) {
                        if(!in_array($list['supplier_id'], $su_ids)) {
                            $su_ids[] = $list['supplier_id'];
                        }
                    } else {
                        $su_ids[] = $list['supplier_id'];
                    }

                    if(!empty($op_mans)) {
                        if(!in_array($list['warehouse_man'], $op_mans)) {
                            $op_mans[] = $list['warehouse_man'];
                        }
                    } else {
                        $op_mans[] = $list['warehouse_man'];
                    }
                }

                //入库类型
                $was_type = DB::select('number','value')->from('statusdics')
                    ->where('table', '=', 'warehouseorders')
                    ->and_where('colum_name', '=', 'type')
                    ->execute('newerp')
                    ->as_array('number', 'value');

                //供应商名字
                /*$client = new PHPRPC_Client();
                $client->useService("http://192.168.5.5:802/profitapi/execute");*/

                if(!empty($su_ids)) {
                    /*$sri = DB::select('id', 'name')->from('suppliers')
                        ->where('id', 'in', $su_ids)->compile('newerp');*/
                    $sri = "SELECT id ,name FROM skusystem_suppliers WHERE id in ('".join("','",$su_ids)."')";
                    $su_names = DB::query(Database::SELECT,$sri)->execute('newerp')->as_array();
                    //$su_names = json_decode($client->getinfo($sri),TRUE);
                    foreach($su_names as $n){
                        $su_ns[$n['id']] = $n['name'];
                    }
                }

                //操作人员
                empty($op_mans) || $op_ns = DB::select('id', 'nick')->from('users')
                    ->where('id', 'in', $op_mans)
                    ->execute()
                    ->as_array('id', 'nick');

                foreach($lists as $list) {
                    $type = iconv('UTF-8','GB18030//IGNORE', isset($was_type[$list['type']]) ? $was_type[$list['type']] : '');
                    $su_name = iconv('UTF-8','GB18030//IGNORE', isset($su_ns[$list['supplier_id']]) ? $su_ns[$list['supplier_id']] : '');
                    $op_man_name = iconv('UTF-8','GB18030//IGNORE', isset($op_ns[$list['warehouse_man']]) ? $op_ns[$list['warehouse_man']] : '');
                    $list['sku_name'] = iconv('UTF-8','GB18030//IGNORE', $list['sku_name']);
                    $list['sku_standard'] = iconv('UTF-8','GB18030//IGNORE', $list['sku_standard']);
                    $list['remark'] = iconv('UTF-8','GB18030//IGNORE', $list['remark']);
                    $temp = "\"{$list['purchaseorders_id']}\"\t,\"{$list['id']}\"\t,\"{$list['sku']}\"\t,\"{$list['sku_name']}\"\t,\"{$list['storage_position']}\"\t,{$list['warehouse_quantity']},{$list['single_price']},{$list['money']},{$list['cost']},\"{$su_name}\"\t,\"{$list['sku_standard']}\"\t,\"{$list['warehouse_date']}\"\t,\"{$type}\"\t,\"{$list['remark']}\"\t,\"{$op_man_name}\"\t,\"{$list['batch_code']}\"\t\r\n";
                    fwrite($fp,$temp);
                }
            }
            fclose($fp);
        }
        echo $md5;
        exit;
    }


    public function  action_down_out(){
        $condition=$_GET;
        $tablename='deliveryorders';
        set_time_limit(0);
        ini_set('memory_limit','512M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ROMWE")
            ->setLastModifiedBy("ROMWE")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', '订单号');
        $objActSheet->setCellValue('B1', '出库单号');
        $objActSheet->setCellValue('C1', 'SKU');
        $objActSheet->setCellValue('D1', '品名');
        $objActSheet->setCellValue('E1', '储位');
        $objActSheet->setCellValue('F1', '出库量');
        $objActSheet->setCellValue('G1', '单价');
        $objActSheet->setCellValue('H1', '金额');
        $objActSheet->setCellValue('I1', '成本');
        $objActSheet->setCellValue('J1', '规格');
        $objActSheet->setCellValue('K1', '出库时间');
        $objActSheet->setCellValue('L1', '出库类型');
        $objActSheet->setCellValue('M1', '备注');
        $objActSheet->setCellValue('N1', '操作人');
        $pre= Report::getListInfoCondition(Report::getListPre($tablename),$condition,true,$tablename);
        $datas=$pre->execute('newerp')->as_array();
        $time=date('Y-m-d H:i:s');
        for($i=0;$i<count($datas);$i++)
        {
            $key=$i+2;
            $objActSheet->setCellValue('A'.$key, $datas[$i]['order_id']);
            $objActSheet->setCellValue('B'.$key, $datas[$i]['id']);
            $objActSheet->setCellValue('C'.$key, $datas[$i]['sku']);
            $objActSheet->setCellValue('D'.$key, $datas[$i]['sku_name']);
            $objActSheet->setCellValue('E'.$key, $datas[$i]['storage_position']);
            $objActSheet->setCellValue('F'.$key, $datas[$i]['quantity']);
            $objActSheet->setCellValue('G'.$key, $datas[$i]['single_price']);
            $objActSheet->setCellValue('H'.$key, $datas[$i]['money']);
            $objActSheet->setCellValue('I'.$key, $datas[$i]['cost']);
            $objActSheet->setCellValue('J'.$key, $datas[$i]['sku_standard']);
            $objActSheet->setCellValue('K'.$key, $datas[$i]['delivery_date']);
            $objActSheet->setCellValue('L'.$key, Report::getOuttpyeByid($datas[$i]['type']));
            $objActSheet->setCellValue('M'.$key, $datas[$i]['remark']);
            $objActSheet->setCellValue('N'.$key, Report::get_uname($datas[$i]['delivery_man']));
        }
        $objPHPExcel->getActiveSheet()->setTitle('出库报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'出库报表.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function  action_down_in_count(){
        $condition=$_GET;
        $condition['type']=10;
        $tablename='warehouseorders';
        set_time_limit(0);
        ini_set('memory_limit','512M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ROMWE")
            ->setLastModifiedBy("ROMWE")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $method='';
        switch ($condition['group']) {
            case 1:
                $method='cgrk1';
                break;
            case 2:
                $method='cgrk2';
                break;
            case 3:
                $method='cgrk3';
                break;
        }
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $this->$method($objActSheet, $condition, $tablename);
        $objPHPExcel->getActiveSheet()->setTitle('采购入库报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'采购入库报表.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function  action_down_out_count(){
        $condition=$_GET;
        $condition['type']=10;
        $tablename='deliveryorders';
        set_time_limit(0);
        ini_set('memory_limit','512M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ROMWE")
            ->setLastModifiedBy("ROMWE")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objActSheet->setCellValue('B1', '品名');
        $objActSheet->setCellValue('C1', '储位');
        $objActSheet->setCellValue('D1', '出库量');
        $objActSheet->setCellValue('E1', '单价');
        $objActSheet->setCellValue('F1', '金额');
        $objActSheet->setCellValue('G1', '成本');
        $pre= Report::getcountListInfoCondition(Report::getListPre1($tablename),$condition,true,$tablename);
        $datas=$pre->execute('newerp')->as_array();
        $time=date('Y-m-d H:i:s');
        for($i=0;$i<count($datas);$i++)
        {
            $key=$i+2;
            $objActSheet->setCellValue('A'.$key, $datas[$i]['sku']);
            $objActSheet->setCellValue('B'.$key, $datas[$i]['sku_name']);
            $objActSheet->setCellValue('C'.$key, $datas[$i]['storage_position']);
            $objActSheet->setCellValue('D'.$key, $datas[$i]['quantity']);
            $objActSheet->setCellValue('E'.$key, $datas[$i]['single_price']);
            $objActSheet->setCellValue('F'.$key, $datas[$i]['money']);
            $objActSheet->setCellValue('G'.$key, $datas[$i]['cost']);
        }
        $objPHPExcel->getActiveSheet()->setTitle('销售出库统计报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'销售出库统计报表.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**
     *
     * Description 一个简单的封装用来分页
     * @param field_type  bare_field_name
     * @param string $tablename
     * @return array  返回分页数据和视图
     */
    private function fenye($tablename){
        $condition = $_GET;
        $items_per_page = 0;
        if (isset($_GET['per_page']) && !empty($_GET['per_page'])) {
            $items_per_page = $_GET['per_page'];
        } else {
            $items_per_page = 15;
        }
        $pre = Report::getListInfoCondition(Report::getTotlePre($tablename),$condition,false,$tablename);
        $totles = Report::getTotle($pre);
        $pre = Report::getListInfoCondition(Report::getTotleallPre($tablename),$condition,false,$tablename);
        $totlesall = Report::getallTotle($pre);
        $pagination = Pagination::factory(array(
            'total_items' =>$totles,
            'view' => 'pagination/ajax_basic',
            'items_per_page' => $items_per_page,
            'uri'=>$this->request->uri(),
        ));
        if (isset($_GET['page'])&&!empty($_GET['page'])) {
            $offset = ($_GET['page']-1)*$items_per_page;
        } else {
            $offset = 0;
        }
        $pre = Report::getListInfoCondition(Report::getListPre($tablename),$condition,true,$tablename);
        $datas = Report::getList($items_per_page,$offset,$pre);
//        //----yuqing--bengin--
        //入库明细报表添加采购注释
        $pu_ids_arr = Position::array_columns($datas,'purchaseorders_id');
        if (array_sum($pu_ids_arr) > 0){
            $pu_remark_arr = DB::select('id','remark')->from('purchaseorders')->where('id','in',array_unique($pu_ids_arr))
                ->execute('newerp')->as_array('id','remark');
            if($pu_remark_arr){
                foreach ($datas as &$list){
                    $list['pu_remark'] =  isset($pu_remark_arr[$list['purchaseorders_id']])
                        ? $pu_remark_arr[$list['purchaseorders_id']] : '';
                }
            }
        }


//
//        $skuArr         = Position::array_columns($datas,'sku');
//        $skuPositionArr = Position::getSkusPostion($skuArr);
//
//        //如果没有储位就继续使用采购明细表的信息
//        if($skuPositionArr){
//            foreach($datas as &$list){
//                $list['storage_position'] =isset($skuPositionArr[$list['sku']])?$skuPositionArr[$list['sku']] : '';
//            }
//            unset($list);
//        }
        $pager_links = $pagination->render();
        $result=array('datas'=>$datas,'pager_links'=>$pager_links,'totles'=>$totles,'totlesall'=>$totlesall);
        return $result;
    }


    private  function  pubudata($tablename){
        $condition=$_GET;
        $items_per_page=100;
        if(isset($_GET['page'])&&!empty($_GET['page']))
        {
            $offset=($_GET['page']-1)*$items_per_page;
        }else {
            $offset=0;
        }
        $condition['type']=10;
        $pre= Report::getcountListInfoCondition(Report::getTotlePre($tablename),$condition,false,$tablename);
        $totles=Report::getallcountTotle($pre);
        $pre= Report::getListInfoCondition(Report::getTotleallPre($tablename),$condition,false,$tablename);
        $totlesall=Report::getallTotle($pre);
        if($tablename=='warehouseorders'){
            $method='getListPre2';
        }else{
            $method='getListPre1';
        }
        $pre= Report::getcountListInfoCondition(Report::$method($tablename),$condition,true,$tablename);
        $datas=Report::getList($items_per_page,$offset,$pre);
        $result=array('datas'=>$datas,'totlesall'=>$totlesall,'totles'=>count($totles));
        return $result;
    }

    private function  cgrk1($objActSheet,$condition,$tablename){
        $objActSheet->setCellValue('A1', 'SKU');
        $objActSheet->setCellValue('B1', '品名');
        $objActSheet->setCellValue('C1', '储位');
        $objActSheet->setCellValue('D1', '入库量');
        $objActSheet->setCellValue('E1', '金额');
        $objActSheet->setCellValue('F1', '成本');
        $pre= Report::getcountListInfoCondition(Report::getListPre2($tablename),$condition,true,$tablename);
        $datas=$pre->execute('newerp')->as_array();
        $time=date('Y-m-d H:i:s');
        for($i=0;$i<count($datas);$i++)
        {
            $key=$i+2;
            $objActSheet->setCellValue('A'.$key, $datas[$i]['sku']);
            $objActSheet->setCellValue('B'.$key, $datas[$i]['sku_name']);
            $objActSheet->setCellValue('C'.$key, $datas[$i]['storage_position']);
            $objActSheet->setCellValue('D'.$key, $datas[$i]['quantity']);
            $objActSheet->setCellValue('E'.$key, $datas[$i]['money']);
            $objActSheet->setCellValue('F'.$key, $datas[$i]['cost']);
        }
    }
    private function  cgrk2($objActSheet,$condition,$tablename){
        $objActSheet->setCellValue('A1', '供应商');
        $objActSheet->setCellValue('B1', '入库量');
        $objActSheet->setCellValue('C1', '金额');
        $objActSheet->setCellValue('D1', '成本');
        $pre= Report::getcountListInfoCondition(Report::getListPre2($tablename),$condition,true,$tablename);
        $datas=$pre->execute('newerp')->as_array();
        $time=date('Y-m-d H:i:s');
        for($i=0;$i<count($datas);$i++)
        {
            $key=$i+2;
            $objActSheet->setCellValue('A'.$key, Report::getnameByid($datas[$i]['supplier_id'])? Report::getnameByid($datas[$i]['supplier_id']):'未知' );
            $objActSheet->setCellValue('B'.$key, $datas[$i]['quantity']);
            $objActSheet->setCellValue('C'.$key, $datas[$i]['money']);
            $objActSheet->setCellValue('D'.$key, $datas[$i]['cost']);
        }
    }
    private function  cgrk3($objActSheet,$condition,$tablename){
        $objActSheet->setCellValue('A1', '供应商');
        $objActSheet->setCellValue('B1', 'SKU');
        $objActSheet->setCellValue('C1', '品名');
        $objActSheet->setCellValue('D1', '储位');
        $objActSheet->setCellValue('E1', '入库量');
        $objActSheet->setCellValue('F1', '平均单价');
        $objActSheet->setCellValue('G1', '金额');
        $objActSheet->setCellValue('H1', '成本');
        $pre= Report::getcountListInfoCondition(Report::getListPre2($tablename),$condition,true,$tablename);
        $datas=$pre->execute('newerp')->as_array();
        $time=date('Y-m-d H:i:s');
        for($i=0;$i<count($datas);$i++)
        {
            $key=$i+2;
            $objActSheet->setCellValue('A'.$key, Report::getnameByid($datas[$i]['supplier_id'])? Report::getnameByid($datas[$i]['supplier_id']):'未知' );
            $objActSheet->setCellValue('B'.$key, $datas[$i]['sku']);
            $objActSheet->setCellValue('C'.$key, $datas[$i]['sku_name']);
            $objActSheet->setCellValue('D'.$key, $datas[$i]['storage_position']);
            $objActSheet->setCellValue('E'.$key, $datas[$i]['quantity']);
            $objActSheet->setCellValue('F'.$key, round($datas[$i]['money']/$datas[$i]['quantity'],2));
            $objActSheet->setCellValue('G'.$key, $datas[$i]['money']);
            $objActSheet->setCellValue('H'.$key, $datas[$i]['cost']);
        }
    }
}
